package org.jeecg.modules.submodules.mapper;

import io.netty.util.internal.StringUtil;
import org.jeecg.modules.submodules.entity.vo.BigOrdersPageVo;
import org.jeecg.modules.submodules.entity.vo.CarStatisticsVo;
import org.jeecg.modules.submodules.entity.vo.GoodsStatisticsVo;

public class BigOrdersMapperProvider {
    public String getBigOrdersPageVoList(BigOrdersPageVo pageVo){
        String sql = "SELECT\n" +
                "\tbo.id,\n" +
                "\tbo.BIG_ORDERS_NAME,\n" +
                "\nbo.BIG_ORDERS_COMMENT,\n" +
                "\tGROUP_CONCAT( o.ORDERS_NUMBER ) ordersNumber,\n" +
                "\tbo.BIG_ORDERS_STATUS,\n" +
                "\to.CREATED_BY,\n" +
                "\to.CREATED_TIME,\n" +
                "\tGROUP_CONCAT( g.GOODS_NAME ) goodsName,\n" +
                "\tstartI.depart_name startName,\n" +
                "\tGROUP_CONCAT(endI.depart_name) endName,\n" +
                "\tcar.CAR_NUMBER,\n" +
                "\tcar.realname\n" +
                "FROM\n" +
                "\tbig_orders bo\n" +
                "\tLEFT JOIN orders_connect oc ON oc.BIG_ORDERS_ID = bo.id\n" +
                "\tLEFT JOIN orders o ON o.id = oc.ORDERS_ID\n" +
                "\tLEFT JOIN (\n" +
                "\tSELECT\n" +
                "\t\tg.id,\n" +
                "\t\tg.GOODS_NAME,\n" +
                "\t\to.id ordersId \n" +
                "\tFROM\n" +
                "\t\tgoods g\n" +
                "\t\tLEFT JOIN orders_goods og ON og.GOODS_ID = g.id\n" +
                "\t\tLEFT JOIN orders o ON o.id = og.ORDERS_ID \n" +
                "\t) g ON g.ordersId = oc.ORDERS_ID\n" +
                "\tLEFT JOIN (\n" +
                "\tSELECT\n" +
                "\t\tsd.id,\n" +
                "\t\tsd.depart_name,\n" +
                "\t\to.id orderId \n" +
                "\tFROM\n" +
                "\t\tsys_depart sd\n" +
                "\t\tLEFT JOIN orders_start os ON os.START_ID = sd.id\n" +
                "\t\tLEFT JOIN orders o ON o.id = os.ORDERS_ID \n" +
                "\t) startI ON startI.orderId = oc.ORDERS_ID\n" +
                "\tLEFT JOIN (\n" +
                "\tSELECT\n" +
                "\t\tcar.id,\n" +
                "\t\tcar.CAR_NUMBER,\n" +
                "\t\tsu.id userId,\n" +
                "\t\tsu.username,\n" +
                "\t\tsu.realname,\n" +
                "\t\tbo.id bigOrdersId \n" +
                "\tFROM\n" +
                "\t\tcar\n" +
                "\t\tLEFT JOIN big_orders_driver bod ON bod.CAR_ID = car.id\n" +
                "\t\tLEFT JOIN sys_user su ON su.id = bod.DRIVER_ID\n" +
                "\t\tLEFT JOIN big_orders bo ON bo.id = bod.BIG_ORDERS_ID \n" +
                "\t) car ON car.bigOrdersID = bo.ID \n" +
                "\tleft join(\n" +
                "\tSELECT\n" +
                "\t\tsd.id,\n" +
                "\t\tsd.depart_name,\n" +
                "\t\to.id orderId \n" +
                "\tFROM\n" +
                "\t\tsys_depart sd\n" +
                "\t\tLEFT JOIN orders_end os ON os.END_ID = sd.id\n" +
                "\t\tLEFT JOIN orders o ON o.id = os.ORDERS_ID \n" +
                "\t) endI on endI.orderId = oc.ORDERS_ID\n";
        StringBuilder builder = new StringBuilder(sql);
        builder.append("\n where bo.DEL_FLAG != 1\n");
        if (!StringUtil.isNullOrEmpty(pageVo.getBigOrdersName())){
            builder.append("\nAND" +
                    "\tbo.BIG_ORDERS_NAME like '%"+pageVo.getBigOrdersName()+"%'");
        }
        if (!StringUtil.isNullOrEmpty(pageVo.getCarNumber())){
            builder.append("\nAND" +
                    "\t\tcar.CAR_NUMBER like '%"+pageVo.getCarNumber()+"%'");
        }
        if (!StringUtil.isNullOrEmpty(pageVo.getRealname())){
            builder.append("\nAND" +
                    "\t\tcar.realname like '%"+pageVo.getRealname()+"%'");
        }
        if (!StringUtil.isNullOrEmpty(pageVo.getOrdersNumber())){
            builder.append("\nAND" +
                    "\t\to.ORDERS_NUMBER like '%"+pageVo.getOrdersNumber()+"%'");
        }
        if (!StringUtil.isNullOrEmpty(pageVo.getGoodsName())){
            builder.append("\nAND" +
                    "\t\tg.GOODS_NAME  like '%"+pageVo.getGoodsName()+"%'");
        }
        if (!StringUtil.isNullOrEmpty(pageVo.getStartName())){
            builder.append("\nAND" +
                    "\t\tstartI.depart_name like '%"+pageVo.getStartName()+"%'");
        }
        if (!StringUtil.isNullOrEmpty(pageVo.getEndName())){
            builder.append("\nAND" +
                    "\t\tendI.depart_name like '%"+pageVo.getEndName()+"%'");
        }
        if (!StringUtil.isNullOrEmpty(pageVo.getStartDate())&&!StringUtil.isNullOrEmpty(pageVo.getEndDate())){
            String startDate = "'"+pageVo.getStartDate()+"'";
            String endDate = "'"+pageVo.getEndDate()+"'";
            builder.append("\nAND bo.CREATED_TIME between "+startDate+" and "+endDate+"\n");
        }
        if (!StringUtil.isNullOrEmpty(pageVo.getCreatedBy())){
            builder.append("\nAND" +
                    "\t\tbo.created_by like '%"+pageVo.getCreatedBy()+"%'");
        }
        if (!StringUtil.isNullOrEmpty(pageVo.getBigOrdersStatus())){
            builder.append("\nAND" +
                    "\t\tbo.BIG_ORDERS_STATUS = 6");
        }
            builder.append("\nGROUP BY\n" +
                    "\tbo.ID ");
            builder.append("\norder by\n" +
                    "bo.CREATED_TIME DESC");
        return builder.toString();
    }

    /**
     * 统计分析 统计车辆接单数(根据 年 月 周)
     */
    public String getCarReceivingOrders(CarStatisticsVo vo){
        String sql = "SELECT\n" +
                "\tcount( bo.id ) counts,\n" +
                "\tbo.ID,\n" +
                "\tbo.CREATED_TIME,\n" +
                "\tcar.CAR_NUMBER \n" +
                "FROM\n" +
                "\tcar\n" +
                "\tLEFT JOIN big_orders_driver bod ON bod.CAR_ID = car.ID\n" +
                "\tLEFT JOIN big_orders bo ON bo.id = bod.BIG_ORDERS_ID ";
        StringBuilder builder = new StringBuilder(sql);
        builder.append("\nwhere bo.del_flag = 0 AND bo.big_orders_status = 6\n");
        if (vo.getFlag()!=null&&StringUtil.isNullOrEmpty(vo.getStartDate())&&StringUtil.isNullOrEmpty(vo.getEndDate())){
            //查询最近七天的记录
            if (vo.getFlag()==1){
                builder.append("\nAND \tTO_DAYS( NOW( ) ) - TO_DAYS( \tbo.CREATED_TIME ) <= 7\n");
            }
            //查询本月记录
            if (vo.getFlag()==2){
                builder.append("\nAND DATE_FORMAT( bo.CREATED_TIME, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )\n");
            }
            //查询本年记录
            if (vo.getFlag()==3){
                builder.append("\nAND DATE_FORMAT( bo.CREATED_TIME, '%Y' ) = DATE_FORMAT( CURDATE( ) , '%Y' )\n");
            }
        }else if (!StringUtil.isNullOrEmpty(vo.getStartDate())&&!StringUtil.isNullOrEmpty(vo.getEndDate())){
            String start ="'"+vo.getStartDate()+"'";
            String end ="'"+vo.getEndDate()+"'";
            builder.append("\n" +
                    "AND\n" +
                    "\tbo.CREATED_TIME BETWEEN "+start+" \n" +
                    "\tAND "+end+" ");
        }
        builder.append("\nGROUP BY\n" +
                "\tcar.CAR_NUMBER \n" +
                "ORDER BY\n" +
                "\tcounts DESC");
        return builder.toString();
    }
    /**
     * 统计货物实际重量
     */
    public String getAllGoodsWeight(GoodsStatisticsVo vo){
        String sql = "SELECT\n" +
                "\tsum( o.ORDERS_ACTUAL_WEIGHT ) weight,\n" +
                "\tg.GOODS_NAME \n" +
                "FROM\n" +
                "\tgoods g\n" +
                "\tLEFT JOIN orders_goods og ON og.GOODS_ID = g.id\n" +
                "\tLEFT JOIN orders o ON o.id = og.ORDERS_ID\n" +
                "\tLEFT JOIN orders_connect oc ON oc.orders_id = o.id\n" +
                "\tLEFT JOIN big_orders bo ON bo.id = oc.BIG_ORDERS_ID ";
        StringBuilder builder = new StringBuilder(sql);
        builder.append("\n where bo.DEL_FLAG = 0 AND bo.BIG_ORDERS_STATUS = 6" );
        if (vo.getFlag()!=null&&StringUtil.isNullOrEmpty(vo.getStartDate())&&StringUtil.isNullOrEmpty(vo.getEndDate())){
            //查询最近七天的记录
            if (vo.getFlag()==1){
                builder.append("\nAND \tTO_DAYS( NOW( ) ) - TO_DAYS( \tbo.CREATED_TIME ) <= 7\n");
            }
            //查询本月记录
            if (vo.getFlag()==2){
                builder.append("\nAND DATE_FORMAT( bo.CREATED_TIME, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )\n");
            }
            //查询本年记录
            if (vo.getFlag()==3){
                builder.append("\nAND DATE_FORMAT( bo.CREATED_TIME, '%Y' ) = DATE_FORMAT( CURDATE( ) , '%Y' )\n");
            }
        }else if (!StringUtil.isNullOrEmpty(vo.getStartDate())&&!StringUtil.isNullOrEmpty(vo.getEndDate())){
            String start ="'"+vo.getStartDate()+"'";
            String end ="'"+vo.getEndDate()+"'";
            builder.append("\n" +
                    "AND\n" +
                    "\tbo.CREATED_TIME BETWEEN "+start+" \n" +
                    "\tAND "+end+" ");
        }

        builder.append("\nGROUP BY\n" +
                "\tg.id \n" +
                "ORDER BY\n" +
                "\tweight DESC");
        return builder.toString();
    }
    /**
     * 查周转率
     */
    public String getZhouzhuan(){
        return "SELECT\n" +
                "\t( num.nums / car.counts ) zhouzhuan \n" +
                "FROM\n" +
                "\t( SELECT count( 0 ) counts FROM car ) car,\n" +
                "\t(\n" +
                "SELECT\n" +
                "\tc.id,\n" +
                "\tc.CAR_NUMBER,\n" +
                "\tCOUNT( c.id ) nums,\n" +
                "\tbo.CREATED_TIME \n" +
                "FROM\n" +
                "\tbig_orders bo\n" +
                "\tLEFT JOIN big_orders_driver bod ON bod.BIG_ORDERS_ID = bo.id\n" +
                "\tLEFT JOIN car c ON c.id = bod.CAR_ID \n" +
                "WHERE\n" +
                "\tbod.CAR_ID IS NOT NULL \n" +
                "\tAND\n" +
                "\tto_days(bo.CREATED_TIME) = to_days( now( ) )\n" +
                "\tAND\n" +
                "\tbo.DEL_FLAG = 0\n" +
                "\t) num ";
    }


}
